package batch.import_user;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.*;
import org.apache.log4j.*;

import java.util.ArrayList;
import java.sql.*;
import java.io.*;

import batch.common.db.ConnectionManager;

public class makeMemberFileForFra
{
    private final String FILE_NAME     = "/home01/sfc/batch/import_user/data/SFC-RF2006-results_Europe.txt";
    private final String NEW_FILE_NAME = "SFC-RF2006-results_Europe.txt";



    public static void main(String[] args)
    {
        Logger.getLogger("import_user.makeMemberFileForFra").info("( * ) Start.");

        makeMemberFileForFra obj = new makeMemberFileForFra();
        obj.init();

        Logger.getLogger("import_user.makeMemberFileForFra").info("( * ) End.");
    }


    public void init(){
        Connection conn = null;

        BufferedReader bfrdForFile = null;
        OutputStreamWriter fw = null;

        ArrayList params = new ArrayList();

        StringBuffer sbfLine = new StringBuffer();


        String strmakeMemberFileForFra = "";
        StringBuffer sQuery = new StringBuffer()
                             .append("SELECT FIRSTNAME   \n")
                             .append("     , LASTNAME    \n")
                             .append("     , BIRTH       \n")
                             .append("     , GENDER      \n")
                             .append("     , EMAIL       \n")
                             .append("     , HP_MODEL    \n")
                             .append("     , HP_NUM      \n")
                             .append("     , ADDRESS     \n")
                             .append("     , POSTCODE    \n")
                             .append("     , COUNTRY     \n")
                             .append("  FROM G_MB_MEMBER \n")
                             .append(" WHERE ID = ?      \n");

        Logger.getLogger("import_user.makeMemberFileForFra").info(FILE_NAME);

        try{
            bfrdForFile = new BufferedReader(new InputStreamReader(new FileInputStream(FILE_NAME), "ISO-8859-1"));

            //fw = new OutputStreamWriter(new FileOutputStream("/home01/sfc/batch/import_user/data/file/member.txt", true), "UTF-8");
            fw = new OutputStreamWriter(new FileOutputStream("/home01/sfc/batch/import_user/data/file/" + NEW_FILE_NAME, true), "UTF-8");

            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(true);

            QueryRunner qRunner = new QueryRunner();

            for(int i=0; (strmakeMemberFileForFra = bfrdForFile.readLine()) != null; i++) {
                //String arrStrData = StringUtils.deleteWhitespace(strmakeMemberFileForFra);
                String arrStrData[] = StringUtils.splitPreserveAllTokens(strmakeMemberFileForFra, "\t");

                params.clear();
                params.add(arrStrData[1]);  //ID

                ResultSetHandler rsh = new ArrayHandler();

                Object[] sRE = (Object[])qRunner.query(conn, sQuery.toString(), params.toArray(), rsh);

                sbfLine.setLength(0);

                for(int x=0; x<arrStrData.length-1; x++){
                    sbfLine.append(arrStrData[x]               ).append("\t");
                }

                if(sRE != null){
                    sbfLine.append(ObjectUtils.toString(sRE[0])).append("\t")
                           .append(ObjectUtils.toString(sRE[1])).append("\t")
                           .append(ObjectUtils.toString(sRE[2])).append("\t")
                           .append(ObjectUtils.toString(sRE[3])).append("\t")
                           .append(ObjectUtils.toString(sRE[5])).append("\t")
                           .append(ObjectUtils.toString(sRE[4])).append("\t")
                           .append(ObjectUtils.toString(sRE[6])).append("\t")
                           .append(this.getCountry(ObjectUtils.toString(sRE[9]))).append("\n");
                }
                else{
                    sbfLine.append("").append("\t")
                           .append("").append("\t")
                           .append("").append("\t")
                           .append("").append("\t")
                           .append("").append("\t")
                           .append("").append("\t")
                           .append("").append("\t")
                           .append("").append("\n");
                }

                fw.write(sbfLine.toString());

                if(i % 500 == 0){
                    Logger.getLogger("import_user.makeMemberFileForFra").info("here we are == " + i);
                }
            }
        }
        catch(Exception ex) {
            ex.printStackTrace();
            Logger.getLogger("import_user.makeMemberFileForFra").info(ex.toString());
        }
        finally {
            if (bfrdForFile != null) try { bfrdForFile.close(); } catch (Exception se) {}
            if (fw != null)   try { fw.close();   } catch (Exception se) {}
            if (conn != null) try { conn.close(); } catch (Exception se) {}
        }
    }

    public String getCountry(String sCountry)
    {
        if(sCountry.equals("0102064"))
            return "UK";
        if(sCountry.equals("0102004"))
            return "Belgium";
        if(sCountry.equals("0102017"))
            return "France";
        if(sCountry.equals("0102018"))
            return "Germany";
        if(sCountry.equals("0102029"))
            return "Italy";
        if(sCountry.equals("0102041"))
            return "Mexico";
        if(sCountry.equals("0102042"))
            return "Netherlands";
        if(sCountry.equals("0102049"))
            return "Portugal";
        if(sCountry.equals("0102051"))
            return "Russia";
        if(sCountry.equals("0102056"))
            return "South Africa";
        if(sCountry.equals("0102057"))
            return "Spain";
        if(sCountry.equals("0102063"))
            return "Middle East";
        if(sCountry.equals("0102065"))
            return "Ukraine";
        if(sCountry.equals("0102999"))
            return "Europe";
        if(sCountry.equals("0102019"))
            return "Greece";
        if(sCountry.equals("0102998"))
            return "Nordic";
        if(sCountry.equals("0102008"))
            return "Chile";
        if(sCountry.equals("0102005"))
            return "Brazil";

        return "ETC";
    }
}
